# usage: query.py db_file ontology output_file query
# На вход должны приходить уже нормальные формы

import sqlite3, sys, os, re

xmlTemplatePath = "../Data/query_result_template.xml"

def ConnectToDB(dbName):
    if (not os.path.exists(dbName)):
        print("Не найден файл базы данных, проверьте его доступность")
        sys.exit(1)
    
    db = sqlite3.connect(dbName)
    if (db == None):
        print("Ошибка подключения к базе данных")
        sys.exit(1)
        
    db.row_factory = sqlite3.Row
    
    return db

def GetFromDB (db, query):
    conn = db.cursor()
    conn.execute(query)
    return conn.fetchall()

def GetResultEvents(events):
    output = ""
    i = 0
    for event in events:
        i += 1
        (year, month, day) = event["date"].split("/")
        team1 = event["team1"]
        team2 = event["team2"]
        title = "матч %s-%s (%s.%s.%s)" % (team1, team2, day, month, year)
        if (event["result"].find(":") != -1):
            (result1, result2) = event["result"].split(":")
        else:
            result1 = result2 = ""
        stadium = event["place"]
        sport_type = event["sport"]
        url = ""
        output += "<event id=\"%d\" \n\ttitle=\"%s\" \n\ttype=\"%s\" \n\tyear=\"%s\" \n\tmonth=\"%s\" \n\tday=\"%s\" \n\tteam1=\"%s\" \n\tresult1=\"%s\"\n\t team2=\"%s\" \n\tresult2=\"%s\"\n\t placestadium=\"%s\" url=\"%s\"\n>\n" % (i, title, sport_type, year, month, day, team1, result1, team2, result2, stadium, url)
        
        
    return output

def WriteResult(filename, events):
    with open(xmlTemplatePath) as templateFile:
        with open(filename, "w") as outFile:
            for line in templateFile:
                if (line.strip("\n") == "<event/>"):
                    outFile.write(GetResultEvents(events))
                else:
                    outFile.write(line)
        

sys.argv = ("\"" + "\" \"".join(sys.argv) + "\"")[1:-1].split("\" \"")
if (len(sys.argv) < 5):
    print ("использование: query.py db_file ontology_file output_file query")
    sys.exit(1)
    
query = sys.argv[4].lower()

db = ConnectToDB(sys.argv[1])

teams = GetFromDB (db, "SELECT id, sporttypeid, lower(name) as name FROM teams")
teams_syn = GetFromDB (db, "SELECT teamsynonyms.id as syn_id, lower(teams.name) as team_name, teams.id as id, lower(synonym) as synonym, teamid FROM teamsynonyms JOIN teams ON teamid = teams.id")
stadiums = GetFromDB (db, "SELECT id, lower(name) as name, lower(place) as place, team FROM stadiums")

eventQuery = "SELECT sporttypes.name as sport, date, team1, team2, place, result from events, sporttypes where sporttypeid = sporttypes.id and ((team1 = '%s' or team2 = '%s') and place = '%s')";

query_team = {}
query_stadium = []
query_matchtype = []

for team in teams:
    if (team["name"] == ""):
        continue
    if team["name"].lower() in query.split(" "):
        if (not team["name"].lower() in query_team):
            query_team [team["name"]] = []
        query_team[team["name"]].append(team)
        

#for team_syn in teams_syn:
    #if (team_syn["synonym"].lower() == ""):
        #continue
    #if team_syn["synonym"].lower() in query and team_syn["synonym"].lower() != " ":
        #if (not team["name"].lower() in query_team):
            #query_team [team_syn["team_name"].lower()] = []
        #query_team[team_syn["team_name"].lower()].append(team_syn)
        
for stadium in stadiums:
    if (stadium["name"].lower() == ""):
        continue
    if (stadium["name"].lower() in query):
        query_stadium.append(stadium)

query_stadium.append("")

events = []
for team1 in query_team:
    for team2 in query_team:
        #for stadium in query_stadium:
        data = GetFromDB(db, eventQuery % (team1.lower(), team2.lower(), ""))            
        if (data):
            for row in data:
                events.append(row)
            
WriteResult(sys.argv[3], events)